﻿
SET QUOTED_IDENTIFIER ON
GO

SET ANSI_NULLS ON
GO

/*
 * Define the function fn_GetDayValue
 */
DECLARE @sql nvarchar(1024);
SET @sql = N'
CREATE FUNCTION [dbo].[fn_GetDayValue]
(
    @DateTime datetime
)
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
RETURN YEAR(@DateTime)*10000 + MONTH(@DateTime) * 100 + DAY(@DateTime);
END
';
IF object_id(N'dbo.fn_GetDayValue') IS NULL
BEGIN
	exec(@sql)
END
GO


IF object_id(N'dbo.UDS_WorkAttendence_Data', 'U') IS NULL
BEGIN
    CREATE TABLE [dbo].[UDS_WorkAttendence_Data] (
        [WorkDate]       datetime NOT NULL,
        [DayValue]		 as (dbo.fn_GetDayValue(WorkDate)) PERSISTED,
        [StaffID]        int      NOT NULL,
        [OnDuty]         datetime NULL,
        [OffDuty]        datetime NULL,
        [OnDuty_Status]  bit      NULL,
        [OffDuty_Status] bit      NULL CONSTRAINT [DF_UDS_WorkAttendence_Data_OffDuty_Status] default(1),
        [OnDuty_MemoID]  bigint   NULL,
        [OffDuty_MemoID] bigint   NULL,
        [ID]             bigint   NOT NULL identity(1, 1),

        CONSTRAINT [PK_UDS_WorkAttendence_Data] PRIMARY KEY NONCLUSTERED ([ID] asc)
    );
    CREATE UNIQUE CLUSTERED INDEX [IX_UDS_WorkAttendence_Data_Staff_DayValue] ON dbo.UDS_WorkAttendence_Data ( StaffID asc, DayValue asc);
END
GO
